package com.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.dao.SubstanceDao;
import com.po.Substance;
import com.po.User;
import com.utils.DataSourceUtils;

public class SubstanceDaoImpl implements SubstanceDao{

	public SubstanceDaoImpl() {
		// TODO Auto-generated constructor stub
	}

	@Override
	public Substance findById(String id) throws SQLException {
		QueryRunner qr = new  QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from substance where sid = ?";
		return qr.query(sql, new BeanHandler(Substance.class), id);
	}

	@Override
	public Substance findByType(String type) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	
	public List<Substance> findByStype(String type) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from substance where stype=?";
		return qr.query(sql, new BeanListHandler<Substance>(Substance.class),type);
	}

	@Override
	public Substance findByName(String name) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Substance> findAll() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}
	
	public List<Substance> findAllByScid(String scid) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from substance where scid=?";
		return qr.query(sql, new BeanListHandler<Substance>(Substance.class),scid);
	}

	@Override
	public List<Substance> findAllByPage(int currPage, int pageSize)
			throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from substance limit ?,?";
		return qr.query(sql, new BeanListHandler<Substance>(Substance.class), (currPage-1)*pageSize,pageSize);
	}
	
	@Override
	public List<Substance> findByCondition(String name, String kw,
			int currPage, int pageSize) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from substance where "+name+"=? limit ?,?";	
		return qr.query(sql, new BeanListHandler<Substance>(Substance.class), kw,(currPage-1)*pageSize,pageSize);
	}

	public List<Substance> findByConditionAndLike(String name, String kw,int currPage, int pageSize)
			throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select * from substance where "+name+" like ? limit ?,?";	
		return qr.query(sql, new BeanListHandler<Substance>(Substance.class), "%"+kw+"%",(currPage-1)*pageSize,pageSize);
	}
	
	@Override
	public boolean save(Substance s) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="insert into substance values(?,?,?,?,?,?,?,?,?,?,?) ";
		int i = qr.update(sql, s.getSid(),s.getStitle(),s.getStype(),s.getSdate()
				,s.getSauthor(),s.getScomment(),s.getSvote(),s.getScontent(),s.getSimgurl(),s.getScid(),s.getSup());
		if (i==1) {
			b = true;
		}
		return b;
	}

	public int getCountByCondition(String name ,String kw) throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select count(*) from substance where "+name+"=?";
		return ((Long)qr.query(sql, new ScalarHandler(),kw)).intValue();
	}
	@Override
	public boolean update(Substance entity) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean deletesid(String sid) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="delete from substance where sid=?";
		int i = qr.update(sql,sid);
		if (i==1) {
			b = true;
		}
		return b;
	}

	@Override
	public int getCount() throws SQLException {
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql="select count(*) from substance";
		return ((Long)qr.query(sql, new ScalarHandler())).intValue();
	}
	
	public boolean updateSup(String sid,Byte sup) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update substance set sup=? where sid=?";
		int i = qr.update(sql,sup,sid);
		if (i==1){
			b = true;
		}
		return b;
	}
	
	public boolean updateScomment(String sid,Byte sc) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update substance set scomment = ? where sid = ?";
		int i = qr.update(sql,sc,sid);
		if (i==1){
			b = true;
		}
		return b;
	}
	
	public boolean updateSvote(String sid,Byte sc) throws SQLException {
		boolean b = false;
		QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "update substance set svote = ? where sid = ?";
		int i = qr.update(sql,sc,sid);
		if (i==1){
			b = true;
		}
		return b;
	}

	@Override
	public boolean delete(Substance entity) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}
}
